﻿#include "sqliteutil.h"

SQLiteUtil::SQLiteUtil(const QString &databaseName,QObject *parent)
{
    FileOperator fileOperator;
    this->initConnectionType(SqlType::SQLITE);
    if(fileOperator.createFile(databaseName))
    {
        this->setDataBaseName(databaseName);
    }
    this->openDatabase();
}

SQLiteUtil::~SQLiteUtil(){

}

void SQLiteUtil::initLoginDB(){
    //用户信息
    this->executeQuery(R"(
        CREATE TABLE IF NOT EXISTS login (
            email VARCHAR(20) PRIMARY KEY,
            headImgUrl TEXT NOT NULL,
            password VARCHAR(255) ,
            userName VARCHAR(32) NOT NULL,
            isAuto TINYINT CHECK (isAuto IN (0, 1)) DEFAULT 0,
            isRemember TINYINT CHECK (isRemember IN (0, 1)) DEFAULT 0,
            sign VARCHAR(30),
            alterTime BIGINT NOT NULL,
            loginTime BIGINT NOT NULL
        );
    )");
}
void SQLiteUtil::initDataBaseDB(){
    //用户的好友信息
    this->executeQuery(R"(
        create table if not exists friends(
            friendId VARCHAR(20) PRIMARY KEY,
            userName VARCHAR(32) NOT NULL,
            noteName VARCHAR(32) ,
            sign VARCHAR(30),
            alterTime BIGINT NOT NULL
        );
    )");
    //信息表
    /*isSelf:
        0表示不是自己发的信息
        1表示是自己发的信息(默认)
    */
    /*isSend:
        0表示服务器未答应(默认)
        1表示文本信息已经发送成功
        2表示文件信息已经发送成功
        3表示文本信息和文件信息都发送成功
        一般来说中间态为2，表示未完成
    */
    this->executeQuery(R"(
        CREATE TABLE IF NOT EXISTS message(
            msgId VARCHAR(32) PRIMARY KEY,
            isSelf TINYINT NOT NULL CHECK (isSelf IN (0, 1)) DEFAULT 1,
            isSend TINYINT NOT NULL CHECK (isSend IN (0, 1, 2, 3)) DEFAULT 0,
            type VARCHAR(10) NOT NULL,
            content TEXT ,
            createTime BIGINT NOT NULL
        );
    )");
    //会话表(用户的好友的会话信息,好友发给自己的信息，自己发给好友的信息）
    this->executeQuery(R"(
        CREATE TABLE IF NOT EXISTS session(
            friendId VARCHAR(20) NOT NULL,
            msgId VARCHAR(32) UNIQUE NOT NULL,
            FOREIGN KEY (friendId) REFERENCES friends(friendId),
            FOREIGN KEY (msgId) REFERENCES message(msgId),
            PRIMARY KEY (friendId, msgId)
        );
    )");
    this->executeQuery(R"(
        CREATE INDEX idx_friendId ON session(friendId,msgId);
    )");

    //存放申请人信息表
    this->executeQuery(R"(
        CREATE TABLE IF NOT EXISTS applicationMsg(
            friendId VARCHAR(20) NOT NULL,
            userName VARCHAR(32) NOT NULL,
            content TEXT,
            buildTime BIGINT NOT NULL,
            status TINYINT CHECK(status IN(0,1,2)) DEFAULT 0,
            PRIMARY KEY (friendId)
        );
    )");
}

//对friends表进行操作
bool SQLiteUtil::selectOneFriendIsExist(){
    return selectOne("friends","1");
}
bool SQLiteUtil::selectOneFriendIsExist(const QString &friend_id) {
    return selectOne("friends", QString("friendId='%1'").arg(friend_id));
}

QVariantMap SQLiteUtil::selectOneFriendAlterTime(const QString& friend_id) {
    return selectOne({ "alterTime" }, "friends", QString("friendId='%1'").arg(friend_id));
}

QVariantMap SQLiteUtil::selectOneFriendUsername(const QString &friend_id){
    return selectOne({"userName","noteName"},"friends",QString("friendId='%1'").arg(friend_id));
}
QVariantMap SQLiteUtil::selectOneFriendInfo(const QString &friend_id){
    return selectOne({"userName","noteName","sign"},"friends",QString("friendId='%1'").arg(friend_id));
}

QList<QVariantMap> SQLiteUtil::selectAllFriendInfoList(){
    return selectAll({"friendId","userName","noteName","sign"},"friends","1");
}

QList<QVariantMap> SQLiteUtil::selectAllFriendFUsername(){
    return selectAll({"friendId","userName","noteName"},"friends","1");
}

bool SQLiteUtil::updateFriendBind(QVariantMap& map,const QString & friend_id) {
    return updateRecordBind("friends", map, QString("friendId='%1'").arg(friend_id));
}

bool SQLiteUtil::insertFriend(QVariantMap& map) {
    return insertRecord("friends", map);
}

//对session表操作
QList<QVariantMap> SQLiteUtil::selectAllSessionNewInfo(){//查询每个好友交流的最新消息
    return selectAll({"f.friendId AS friendId","userName","noteName","type","content","Max(createTime) AS createTime"},"session","s","message","m","msgId","friends","f","friendId","1 GROUP BY s.friendId ORDER BY createTime ASC");
}

QList<QVariantMap> SQLiteUtil::selectAllSessionMsg(const QString &email){//获取与这个好友所有的信息
    return selectAll({"s.msgId","isSelf","type","isSend","content","createTime"},"session","s","message","m","msgId",QString("s.friendId='%1' ORDER BY m.createTime ASC").arg(email));
}

bool SQLiteUtil::insertSession(QVariantMap& map) {
    return insertRecord("session", map);
}


//对Message表进行操作
QVariantMap SQLiteUtil::selectOneSelfInfo(const QString& msgId) {
    return selectOne({ "friendId","type","content","createTime" }, "session", "s", "message", "m", "msgId", QString("s.msgId='%1'").arg(msgId));
}

QVariantMap SQLiteUtil::selectOneMessageIsSend(const QString &msgId){
    return selectOne({"isSend"},"message",QString("msgId='%1'").arg(msgId));
}

QVariantMap SQLiteUtil::selectOneMessageContent(const QString &msgId){
    return selectOne({"content"},"message",QString("msgId='%1'").arg(msgId));
}

QVariantMap SQLiteUtil::selectOneMessageRecentContent(const QString &email){
    return selectOne({"type","content"},"session","s","message","m","msgId",QString("friendId='%1' ORDER BY createTime DESC LIMIT 1").arg(email));
}

QVariantMap SQLiteUtil::selectOneMessageType(const QString &msgId){
    return selectOne({"s.friendId","isSelf","type"},"session","s","message","m","msgId",QString("s.msgId='%1'").arg(msgId));
}

QVariantMap SQLiteUtil::selectOneMessageRecentCreateTime(){
    return selectOne({"createTime"},"message","1 ORDER BY createTime DESC");
}

bool SQLiteUtil::updateMessage(QVariantMap &map,const QString &msgId){
    return updateRecord("message",map,QString("msgId='%1'").arg(msgId));
}

bool SQLiteUtil::insertMessage(QVariantMap& map) {
    return insertRecord("message", map);
}


//对login表进行操作
QList<QVariantMap> SQLiteUtil::selectAllAccoutDown() {
    return selectAll({ "email","headImgUrl","userName" }, "login", QString("1 ORDER BY %1 DESC").arg("loginTime"));
}

bool SQLiteUtil::selectOneLoginIsExist(const QString &email){
    return selectOne("login",QString("email='%1'").arg(email));
}

QVariantMap SQLiteUtil::selectOneLoginRecentInfo(){
    return selectOne({"email","headImgUrl","password","isRemember","isAuto"},"login",QString("1 ORDER BY %1 DESC LIMIT 1").arg("loginTime"));
}

QVariantMap SQLiteUtil::selectOneLoginAlterTime(const QString &email){
    return selectOne({"alterTime"},"login",QString("email='%1'").arg(email));
}

QVariantMap SQLiteUtil::selectOneLoginUserName(const QString &email){
    return selectOne({"userName"},"login",QString("email='%1'").arg(email));
}

QVariantMap SQLiteUtil::selectOneLoginUserInfo(const QString &email){
    return selectOne({"userName","sign"},"login",QString("email='%1'").arg(email));
}

QVariantMap SQLiteUtil::selectOneLoginInfo(const QString &email){
    return selectOne({"headImgUrl","password","isAuto","isRemember"},"login",QString("email='%1'").arg(email));
}

bool SQLiteUtil::updateLoginBind(QVariantMap &map,const QString &email){
    return updateRecordBind("login",map,QString("email='%1'").arg(email));
}

bool SQLiteUtil::insertLogin(QVariantMap &map){
    return insertRecord("login",map);
}

bool SQLiteUtil::selectOneApplicationIsExist(const QString& friend_id){
    return selectOne("applicationMsg", QString("friendId='%1'").arg(friend_id));
}

QVariantMap SQLiteUtil::selectOneApplication(const QString &email){
    return selectOne({"userName","content"},"applicationMsg",QString("friendId='%1'").arg(email));
}

QString SQLiteUtil::selectOneApplicationBuildTime(const QString& email){
    QVariantMap map=selectOne({"buildTime"},"applicationMsg",QString("friendId='%1'").arg(email));
    return map["buildTime"].toString();
}

QString SQLiteUtil::selectOneApplicationContent(const QString& email){
    QVariantMap map=selectOne({"content"},"applicationMsg",QString("friendId='%1'").arg(email));
    return map["content"].toString();
}

bool SQLiteUtil::updateApplication(QVariantMap &map,const QString friend_id){
    return updateRecordBind("applicationMsg",map,QString("friendId='%1'").arg(friend_id));
}

bool SQLiteUtil::insertApplication(QVariantMap &map){
    return insertRecord("applicationMsg",map);
}
